
package com.compuvest.admin.update;

// <editor-fold defaultstate="collapsed" desc="import">
import com.compuvest.admin.request.MultiPart;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
// </editor-fold>

public class MySQL implements java.io.Serializable {

// <editor-fold defaultstate="collapsed" desc="init variables">
    public static Logger log = LoggerFactory.getLogger(MySQL.class);

    private final static String VERSION = "Update Items and Category MySQL v4.0 2008-06-13";

    public final static int MANUFACTURE_W30D =	0x1<<8;     // 0000 0001 0000 0000
    public final static int MANUFACTURE_W45D =	0x1<<9;     // 0000 0010 0000 0000
//    public final static int MANUFACTURE_W90D = 0x1<<10;    // 0000 0100 0000 0000
    public final static int MANUFACTURE_W180D =	0x1<<11;    // 0000 1000 0000 0000
    public final static int MANUFACTURE_W1Y =	0x1<<12;    // 0001 0000 0000 0000
    public final static int MANUFACTURE_W3Y =	0x1<<13;    // 0010 0000 0000 0000
    public final static int MANUFACTURE_W5Y =	0x1<<14;    // 0100 0000 0000 0000

    public final static int MANUFACTURE_WARRANTY = 0xFF00;// 1111 1111 0000 0000 select manufacture warranty

    public final static String W30DAYS =   "30 Days";
    public final static String W45DAYS =   "45 Days";
    public final static String W60DAYS =   "60 Days";
    public final static String W90DAYS =   "90 Days";
    public final static String W180DAYS =  "180 Days";
    public final static String W1YEAR =	   "1 Year";
    public final static String W3YEARS =   "3 Years";
    public final static String W5YEARS =   "5 Years";
    public final static String WLIFETIME = "Life Time";

    private DataSource ds;

    public static Map<String,Integer> vendorTable = new HashMap<String,Integer>(70);
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="MySQL">

    public MySQL() {

        try {
            Context initCtx = new InitialContext();
            ds = (DataSource)initCtx.lookup("jdbc/mysql");
        }
        catch(Exception e) {
            log.error("init\n" + e);
        }

        log.info("Init: " + VERSION);

        initDB();

        initVendorTable();
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="initDB()">

    private void initDB() {

        try (
            Connection conn = ds.getConnection();
            PreparedStatement ps = conn.prepareStatement("SET GLOBAL thread_cache_size=8"))
        {
            ps.addBatch();
            ps.executeBatch();
        }
        catch(SQLException e) {
            log.error("initDB\n" + e); 
        }
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="initVendorTable()">

	private void initVendorTable() {

        try (
            Connection conn = ds.getConnection();
            PreparedStatement ps = conn.prepareStatement("SELECT vendor_id,alias FROM sklad.itemVendor");
            ResultSet rs = ps.executeQuery())
        {
            while(rs.next()) {
                vendorTable.put(rs.getString("alias"), rs.getInt("vendor_id"));
            }
        }
        catch(SQLException e) {
            log.error("initVendorTable\n" + e);
        }
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="setOrderStatus()">

    final static String UPDATE_ORDER_STATUS =
	"UPDATE LOW_PRIORITY sklad.orders o,sklad.customer c " +
        "SET o.import='Y',c.accwareAccount=? " +
	"WHERE o.orderNo=? AND o.customer_id=c.customer_id";

    public void setOrderStatus(MultiPart mrequest) {

        try (
            Connection conn = ds.getConnection();
            PreparedStatement us = conn.prepareStatement(UPDATE_ORDER_STATUS))
        {
            us.setString(1, mrequest.getStringDelQuote("accwareAccount"));
            us.setString(2, mrequest.getStringDelQuote("orderNumber"));
            us.executeUpdate();
    	}
        catch(SQLException e) {
            log.error("setOrderStatus()\n" + e);
        }
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getOrder()">

    static final String GET_ORDER = 
        "SELECT SQL_CALC_FOUND_ROWS rd.order_id orderID,rd.orderNo orderNo,DATE_FORMAT(rd.order_date,'%Y-%m-%d/%T') orderDate," +
        "rd.shipping_price shipPrice,rd.tax_price taxPrice,rd.amount amount,rd.cod cod,rd.warranty warranty," +
        "rd.paymentMethod paymentMethod,rd.shipping_code shipCode,cs.cc_name cardName," +
        "ENCODE(cs.cardNumber,'crypt') cardNumber,cs.cc_number_add cardNumberAdd," +
        "cs.cc_exp_date cardExpDate,cs.bankPhone bankPhone,cs.type type,cs.reseller_code resellerCode," +
        "cs.accwareAccount accwareAccount,cs.shippingAccount shippingAccount,cs.email email," +

        "cs.shippingCompany shippingCompany,cs.shippingFirstName shippingFirstName," +
        "cs.shippingLastName shippingLastName,cs.shipping_address1 shipping_address1," +
        "cs.shipping_address2 shipping_address2,cs.shipping_city shipping_city," +
        "cs.shipping_state shipping_state,csp.description shipping_country,cs.shipping_zip shipping_zip," +
        "cs.shipping_zip4 shipping_zip4,cs.shippingPhone shippingPhone,cs.shippingCell shippingCell," +
        "cs.shippingFax shippingFax," +

        "cs.billingCompany billingCompany,cs.billingFirstName billingFirstName," +
        "cs.billingLastName billingLastName,cs.billing_address1 billing_address1," +
        "cs.billing_address2 billing_address2,cs.billing_city billing_city," +
        "cs.billing_state billing_state,cbl.description billing_country,cs.billing_zip billing_zip," +
        "cs.billing_zip4 billing_zip4,cs.billingPhone billingPhone,cs.billingCell billingCell," +
        "cs.billingFax billingFax," +

        "rd.notes notes,ad.name host,IF(rd.blindShipping,'Y','N') blind,rd.customerPO customerPO," +
        "IF(rd.packingSlip,'Y','N') slip,IF(rd.shipping_sign,'Y','N') signature,rd.trans_fee trfee,rd.trans_id trid," +
        "rd.auth_status authStatus,rd.cjpid cjpid,rd.cjaid cjaid,rd.cjsid cjsid,rd.advertRefID advertRefID," +

        "ip.id couponID,ip.code couponCode,ip.endDate couponDate,ip.procent couponProcent,ip.price couponPrice,ip.count couponCount,ip.used couponUsed, " +
        "rd.amazonNo amazonNo " +

        "FROM sklad.orders rd " +
        "LEFT JOIN sklad.customer cs USING(customer_id) " +
        "LEFT JOIN sklad.uspszone csp ON cs.shipping_country=csp.countryID " +
        "LEFT JOIN sklad.uspszone cbl ON cs.billing_country=cbl.countryID " +
        "LEFT JOIN sklad.itemsPromotionCode ip ON rd.promoCodeID=ip.id " +
        "LEFT JOIN sklad.advert ad ON rd.advert_id=ad.advert_id ";
//        "WHERE rd.import='N' " +
//        "ORDER BY rd.order_date LIMIT 1";

    static final String GET_ITEM = 
        "SELECT i.item_id itemID,RIGHT(i.accwareCatID,12) accwareCode,i.mfr_name mfrName," +
        "i.mfr_number mfrNumber,i.name itemName,i.cost_price costPrice,i.sell_price origPrice,o.sell_price sellPrice," +
        "i.weight weight,o.quantity quantity,o.warrantyDesc warrantyDesc,o.warrantyPrice warrantyPrice," +
        "i.warrantyMask warrantyMask,c.accwareCatID accwareCatID,cs.description conditionDesc," +
        "ps.description packageDesc " +

        "FROM sklad.order_line o " +
        "LEFT JOIN sklad.items i USING(item_id) " +
        "LEFT JOIN sklad.conditions cs ON LEFT(RIGHT(i.accwareCatID,2),1)=cs.id " +
        "LEFT JOIN sklad.packages ps ON RIGHT(RIGHT(i.accwareCatID,2),1)=ps.id " +
        "LEFT JOIN sklad.category c USING(category_id) " +
        "WHERE i.item_id IS NOT NULL AND o.order_id=";

    public String getOrder(MultiPart mrequest) {

        // if check xml from website - no need change coupon (from website = active)
        boolean nochange = mrequest.getParameter("nochange") != null ? false : true;
        // this need for view xml order use orderNumber
        String orderNumber = mrequest.getParameter("oNumber");
        String expression = "rd.import='N'";
        if(orderNumber != null) {
            // this need for view xml order use orderNumber
             expression = "rd.orderNo='" + orderNumber + "'";
        }
        StringBuilder result = new StringBuilder(2500);

        try(Connection conn  = ds.getConnection())
        {
            int orderID = 0;

            PreparedStatement us = conn.prepareStatement(GET_ORDER + "WHERE " + expression + " ORDER BY rd.order_date LIMIT 1");
            ResultSet rs = us.executeQuery();
            if(rs.next()) {

                orderID = rs.getInt("orderID");
                result.append("<orderid>").append(orderID).append("</orderid>");
                result.append("<orderNo>").append(rs.getString("orderNo")).append("</orderNo>");
                result.append("<amazonNo>").append(rs.getString("amazonNo")).append("</amazonNo>");
                result.append("<order_date>").append(rs.getString("orderDate")).append("</order_date>");
                result.append("<ship_price>").append(rs.getDouble("shipPrice")).append("</ship_price>");
                result.append("<blind>").append(rs.getString("blind")).append("</blind>");
                result.append("<packageSlip>").append(rs.getString("slip")).append("</packageSlip>");
                result.append("<taxprice>").append(rs.getDouble("taxPrice")).append("</taxprice>");
                result.append("<amount>").append(rs.getDouble("amount")).append("</amount>");
                result.append("<cod>").append(rs.getString("cod")).append("</cod>");
                result.append("<paymentMethod>").append(rs.getString("paymentMethod")).append("</paymentMethod>");
                result.append("<warranty>").append(rs.getString("warranty")).append("</warranty>");
                result.append("<shipping_code>").append(rs.getString("shipCode")).append("</shipping_code>");
                result.append("<shipping_sign>").append(rs.getString("signature")).append("</shipping_sign>");

                if(rs.getString("trid") != null) {
                    result.append("<trans_id>").append(rs.getString("trid")).append("</trans_id>");
                    result.append("<trans_fee>").append(rs.getString("trfee")).append("</trans_fee>");
                }

                result.append("<card_name>").append(URLEncoder.encode(rs.getString("cardName"), "UTF-8")).append("</card_name>");
                result.append("<card_type>").append(getCardType(rs.getString("cardNumber"))).append("</card_type>");
                result.append("<card_number>").append(rs.getString("cardNumber")).append("</card_number>");
                result.append("<card_number_add>").append(rs.getString("cardNumberAdd")).append("</card_number_add>");
                result.append("<card_exp_date>").append(rs.getString("cardExpDate")).append("</card_exp_date>");
                result.append("<bankphone>").append(URLEncoder.encode(rs.getString("bankPhone"), "UTF-8")).append("</bankphone>");

                result.append("<type>").append(rs.getString("type")).append("</type>");
                result.append("<resellercode>").append(rs.getString("resellerCode")).append("</resellercode>");
                result.append("<accwareAccount>").append(rs.getString("accwareAccount")).append("</accwareAccount>");
                result.append("<shippingAccount>").append(URLEncoder.encode(rs.getString("shippingAccount"), "UTF-8")).append("</shippingAccount>");
                result.append("<purchaseOrder>").append(URLEncoder.encode(rs.getString("customerPO"), "UTF-8")).append("</purchaseOrder>");
                result.append("<email>").append(rs.getString("email")).append("</email>");

                result.append("<address>");
                result.append("<shipping>");
                result.append("<company>").append(URLEncoder.encode(rs.getString("shippingCompany"), "UTF-8")).append("</company>");
                result.append("<firstname>").append(URLEncoder.encode(rs.getString("shippingFirstName"), "UTF-8")).append("</firstname>");
                result.append("<lastname>").append(URLEncoder.encode(rs.getString("shippingLastName"), "UTF-8")).append("</lastname>");
                result.append("<address1>").append(URLEncoder.encode(rs.getString("shipping_address1"), "UTF-8")).append("</address1>");
                result.append("<address2>").append(URLEncoder.encode(rs.getString("shipping_address2"), "UTF-8")).append("</address2>");
                result.append("<city>").append(URLEncoder.encode(rs.getString("shipping_city"), "UTF-8")).append("</city>");
                result.append("<state>").append(rs.getString("shipping_state")).append("</state>");
                result.append("<country>").append(rs.getString("shipping_country")).append("</country>");
                result.append("<zip>").append(rs.getString("shipping_zip")).append("</zip>");
                result.append("<zip4>").append(rs.getString("shipping_zip4")).append("</zip4>");
                result.append("<phone>").append(rs.getString("shippingPhone")).append("</phone>");
                result.append("<cell>").append(rs.getString("shippingCell")).append("</cell>");
                result.append("<fax>").append(rs.getString("shippingFax")).append("</fax>");
                result.append("</shipping>");

                result.append("<billing>");
                result.append("<company>").append(URLEncoder.encode(rs.getString("billingCompany"), "UTF-8")).append("</company>");
                result.append("<firstname>").append(URLEncoder.encode(rs.getString("billingFirstName"), "UTF-8")).append("</firstname>");
                result.append("<lastname>").append(URLEncoder.encode(rs.getString("billingLastName"), "UTF-8")).append("</lastname>");
                result.append("<address1>").append(URLEncoder.encode(rs.getString("billing_address1"), "UTF-8")).append("</address1>");
                result.append("<address2>").append(URLEncoder.encode(rs.getString("billing_address2"), "UTF-8")).append("</address2>");
                result.append("<city>").append(URLEncoder.encode(rs.getString("billing_city"), "UTF-8")).append("</city>");
                result.append("<state>").append(rs.getString("billing_state")).append("</state>");
                result.append("<country>").append(rs.getString("billing_country")).append("</country>");
                result.append("<zip>").append(rs.getString("billing_zip")).append("</zip>");
                result.append("<zip4>").append(rs.getString("billing_zip4")).append("</zip4>");
                result.append("<phone>").append(rs.getString("billingPhone")).append("</phone>");
                result.append("<cell>").append(rs.getString("billingCell")).append("</cell>");
                result.append("<fax>").append(rs.getString("billingFax")).append("</fax>");
                result.append("</billing>");
                result.append("</address>");

                result.append("<notes>").append(URLEncoder.encode(rs.getString("notes"), "UTF-8")).append("</notes>");
                result.append("<host>").append(rs.getString("host")).append("</host>"); // advert name
                result.append("<advertid>").append(rs.getString("advertRefID")).append("</advertid>"); // advert reference (cj aid-pid-sid) (links siteID)
                // advert
                if(rs.getString("cjpid") != null) {
                    result.append("<cjpid>").append(rs.getString("cjpid")).append("</cjpid>");
                }
                if(rs.getString("cjaid") != null) {
                    result.append("<cjaid>").append(rs.getString("cjaid")).append("</cjaid>");
                }
                if(rs.getString("cjsid") != null) {
                    result.append("<cjsid>").append(rs.getString("cjsid")).append("</cjsid>");
                }

                result.append("<ics>").append(rs.getString("authStatus")).append("</ics>");

                // coupon
                // if promotionCode.count=0 first time order give to customer coupon and send to accware (code data) -> CVx0099 07/07/2009
                // if promotionCode.count=1 second time where customer use coupon send to accware (code DISCOUNT) -> CVx0099 DISCOUNT
                if(rs.getString("couponCode") != null) {
                    if(rs.getBoolean("couponUsed") == false) {
                        result.append("<couponCode>CV").append(rs.getString("couponCode")).append(" ").append(rs.getString("couponDate")).append("</couponCode>");

                        if(nochange) { // from website
                            PreparedStatement us2 = conn.prepareStatement("UPDATE sklad.itemsPromotionCode SET used=TRUE WHERE id=?");
                            us2.setInt(1, rs.getInt("couponID"));
                            us2.executeUpdate();
                            us2.close();
                        }
                    }
                    else {
                        result.append("<couponCode>CV").append(rs.getString("couponCode")).append(" DISCOUNT").append("</couponCode>");
                    }
                    result.append("<couponPrice>").append(rs.getString("couponPrice")).append("</couponPrice>");
                    result.append("<couponPercent>").append(rs.getString("couponProcent")).append("</couponPercent>");
                }
            }
            rs.close();
            us.close();

            // count orders
            us = conn.prepareStatement("SELECT FOUND_ROWS() cnt");
            rs = us.executeQuery();
            if(rs.next()) {
                result.append("<countorders>").append(rs.getInt("cnt")).append("</countorders>");
            }
            rs.close();
            us.close();

            // get items in order
            result.append("<items>");

            us = conn.prepareStatement(GET_ITEM + orderID);
            rs = us.executeQuery();
            while(rs.next()) {

                result.append("<item itemid=\"").append(rs.getInt("itemID")).append("\">");
                result.append("<accwareCatID>").append(rs.getString("accwareCatID")).append("</accwareCatID>");
                result.append("<accwareCode>").append(rs.getString("accwareCode")).append("</accwareCode>");
                result.append("<itemName>").append(URLEncoder.encode(rs.getString("itemName"), "UTF-8")).append("</itemName>");
                result.append("<mfrName>").append(URLEncoder.encode(rs.getString("mfrName"), "UTF-8")).append("</mfrName>");
                result.append("<mfrNumber>").append(URLEncoder.encode(rs.getString("mfrNumber"), "UTF-8")).append("</mfrNumber>");
                result.append("<costprice>").append(rs.getDouble("costPrice")).append("</costprice>");
                result.append("<origprice>").append(rs.getDouble("origPrice")).append("</origprice>"); // promoPrice == sellPrice we need origPrice
                result.append("<sellprice>").append(rs.getDouble("sellPrice")).append("</sellprice>");
                result.append("<weight>").append(rs.getDouble("weight")).append("</weight>");
                result.append("<quantity>").append(rs.getInt("quantity")).append("</quantity>");
                result.append("<condition>").append(rs.getString("conditionDesc")).append("</condition>");
                result.append("<package>").append(rs.getString("packageDesc")).append("</package>");

                result.append("<defaultWarranty>");
                // manufacture warranty any bit up ?
                int warrantyMask = rs.getInt("warrantyMask");
                if((warrantyMask & MANUFACTURE_WARRANTY) != 0) {
                    String warrantyDay = "";
                    if((warrantyMask & MANUFACTURE_W30D) != 0) {
                        warrantyDay = W30DAYS;
                    }
                    else if((warrantyMask & MANUFACTURE_W45D) != 0) { warrantyDay = W45DAYS; }
                    else if((warrantyMask & MANUFACTURE_W180D) != 0){ warrantyDay = W180DAYS; }
                    else if((warrantyMask & MANUFACTURE_W1Y) != 0)  { warrantyDay = W1YEAR; }
                    else if((warrantyMask & MANUFACTURE_W3Y) != 0)  { warrantyDay = W3YEARS; }
                    else if((warrantyMask & MANUFACTURE_W5Y) != 0)  { warrantyDay = W5YEARS; }

                    result.append(warrantyDay);
                }
                result.append("</defaultWarranty>");

                result.append("<warrantyDesc>").append(rs.getString("warrantyDesc")).append("</warrantyDesc>");
                result.append("<warrantyPrice>").append(rs.getDouble("warrantyPrice")).append("</warrantyPrice>");

                result.append("</item>");
            }
            result.append("</items>");

            rs.close();
            us.close();
        }
        catch(SQLException | UnsupportedEncodingException e) {
            log.error("getOrders(): " + e);
        }

        return result.toString();
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="Utility">

    private String getCardType(String cardNumber) {

        String cardType = "";
        if(cardNumber.length() > 1) {

            char first = cardNumber.charAt(0);
            switch(first) {
                case '3' : cardType = "AMEX"; break;
                case '4' : cardType = "VISA"; break;
                case '5' : cardType = "MASTERCARD"; break;
                case '6' : cardType = "DISCOVER"; break;
                default : cardType = "";
            }
        }
        return cardType;
    }

// </editor-fold>

}
